{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Happy Customer Bank的目标客户识别\n",
    "\n",
    "任务来源于Data Hackathon 3.x。该问题是一个金融行业的任务：预测Happy Customer Bank对客户发放贷款的概率。 问题描述：https://discuss.analyticsvidhya.com/t/hackathon-3-x-predict-customer-worth-for-happy-customer-bank/3802\n",
    "\n",
    "该问题的优胜解决方案： https://medium.com/data-science-analytics/analytics-vidhya-3-x-hackathon-9f2550b47be6 https://github.com/binga/AnalyticsVidhya_3.X_Hackathon"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 导入必要的工具包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 读入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "train = pd.read_csv('Train.csv')\n",
    "test = pd.read_csv('Test.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Train dataset dimensions: (87020, 26)\n",
      "Test dataset dimensions: (37717, 24)\n"
     ]
    }
   ],
   "source": [
    "print('Train dataset dimensions:', train.shape)\n",
    "print('Test dataset dimensions:', test.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据的基本信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>City</th>\n",
       "      <th>Monthly_Income</th>\n",
       "      <th>DOB</th>\n",
       "      <th>Lead_Creation_Date</th>\n",
       "      <th>Loan_Amount_Applied</th>\n",
       "      <th>Loan_Tenure_Applied</th>\n",
       "      <th>Existing_EMI</th>\n",
       "      <th>Employer_Name</th>\n",
       "      <th>...</th>\n",
       "      <th>Interest_Rate</th>\n",
       "      <th>Processing_Fee</th>\n",
       "      <th>EMI_Loan_Submitted</th>\n",
       "      <th>Filled_Form</th>\n",
       "      <th>Device_Type</th>\n",
       "      <th>Var2</th>\n",
       "      <th>Source</th>\n",
       "      <th>Var4</th>\n",
       "      <th>LoggedIn</th>\n",
       "      <th>Disbursed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ID000002C20</td>\n",
       "      <td>Female</td>\n",
       "      <td>Delhi</td>\n",
       "      <td>20000</td>\n",
       "      <td>23-May-78</td>\n",
       "      <td>15-May-15</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>CYBOSOL</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>G</td>\n",
       "      <td>S122</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ID000004E40</td>\n",
       "      <td>Male</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>35000</td>\n",
       "      <td>7-Oct-85</td>\n",
       "      <td>4-May-15</td>\n",
       "      <td>200000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>TATA CONSULTANCY SERVICES LTD (TCS)</td>\n",
       "      <td>...</td>\n",
       "      <td>13.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6762.9</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>G</td>\n",
       "      <td>S122</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ID000007H20</td>\n",
       "      <td>Male</td>\n",
       "      <td>Panchkula</td>\n",
       "      <td>22500</td>\n",
       "      <td>10-Oct-81</td>\n",
       "      <td>19-May-15</td>\n",
       "      <td>600000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>ALCHEMIST HOSPITALS LTD</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>B</td>\n",
       "      <td>S143</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ID000008I30</td>\n",
       "      <td>Male</td>\n",
       "      <td>Saharsa</td>\n",
       "      <td>35000</td>\n",
       "      <td>30-Nov-87</td>\n",
       "      <td>9-May-15</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>BIHAR GOVERNMENT</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>B</td>\n",
       "      <td>S143</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ID000009J40</td>\n",
       "      <td>Male</td>\n",
       "      <td>Bengaluru</td>\n",
       "      <td>100000</td>\n",
       "      <td>17-Feb-84</td>\n",
       "      <td>20-May-15</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>25000.0</td>\n",
       "      <td>GLOBAL EDGE SOFTWARE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>B</td>\n",
       "      <td>S134</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ID  Gender       City  Monthly_Income        DOB  \\\n",
       "0  ID000002C20  Female      Delhi           20000  23-May-78   \n",
       "1  ID000004E40    Male     Mumbai           35000   7-Oct-85   \n",
       "2  ID000007H20    Male  Panchkula           22500  10-Oct-81   \n",
       "3  ID000008I30    Male    Saharsa           35000  30-Nov-87   \n",
       "4  ID000009J40    Male  Bengaluru          100000  17-Feb-84   \n",
       "\n",
       "  Lead_Creation_Date  Loan_Amount_Applied  Loan_Tenure_Applied  Existing_EMI  \\\n",
       "0          15-May-15             300000.0                  5.0           0.0   \n",
       "1           4-May-15             200000.0                  2.0           0.0   \n",
       "2          19-May-15             600000.0                  4.0           0.0   \n",
       "3           9-May-15            1000000.0                  5.0           0.0   \n",
       "4          20-May-15             500000.0                  2.0       25000.0   \n",
       "\n",
       "                         Employer_Name    ...    Interest_Rate Processing_Fee  \\\n",
       "0                              CYBOSOL    ...              NaN            NaN   \n",
       "1  TATA CONSULTANCY SERVICES LTD (TCS)    ...            13.25            NaN   \n",
       "2              ALCHEMIST HOSPITALS LTD    ...              NaN            NaN   \n",
       "3                     BIHAR GOVERNMENT    ...              NaN            NaN   \n",
       "4                 GLOBAL EDGE SOFTWARE    ...              NaN            NaN   \n",
       "\n",
       "   EMI_Loan_Submitted Filled_Form  Device_Type  Var2  Source  Var4  LoggedIn  \\\n",
       "0                 NaN           N  Web-browser     G    S122     1         0   \n",
       "1              6762.9           N  Web-browser     G    S122     3         0   \n",
       "2                 NaN           N  Web-browser     B    S143     1         0   \n",
       "3                 NaN           N  Web-browser     B    S143     3         0   \n",
       "4                 NaN           N  Web-browser     B    S134     3         1   \n",
       "\n",
       "  Disbursed  \n",
       "0       0.0  \n",
       "1       0.0  \n",
       "2       0.0  \n",
       "3       0.0  \n",
       "4       0.0  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#查看数据前5行\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 87020 entries, 0 to 87019\n",
      "Data columns (total 26 columns):\n",
      "ID                       87020 non-null object\n",
      "Gender                   87020 non-null object\n",
      "City                     86017 non-null object\n",
      "Monthly_Income           87020 non-null int64\n",
      "DOB                      87020 non-null object\n",
      "Lead_Creation_Date       87020 non-null object\n",
      "Loan_Amount_Applied      86949 non-null float64\n",
      "Loan_Tenure_Applied      86949 non-null float64\n",
      "Existing_EMI             86949 non-null float64\n",
      "Employer_Name            86949 non-null object\n",
      "Salary_Account           75256 non-null object\n",
      "Mobile_Verified          87020 non-null object\n",
      "Var5                     87020 non-null int64\n",
      "Var1                     87019 non-null object\n",
      "Loan_Amount_Submitted    52407 non-null float64\n",
      "Loan_Tenure_Submitted    52407 non-null float64\n",
      "Interest_Rate            27726 non-null float64\n",
      "Processing_Fee           27420 non-null float64\n",
      "EMI_Loan_Submitted       27726 non-null float64\n",
      "Filled_Form              87020 non-null object\n",
      "Device_Type              87020 non-null object\n",
      "Var2                     87020 non-null object\n",
      "Source                   87020 non-null object\n",
      "Var4                     87020 non-null int64\n",
      "LoggedIn                 87020 non-null int64\n",
      "Disbursed                87019 non-null float64\n",
      "dtypes: float64(9), int64(4), object(13)\n",
      "memory usage: 17.3+ MB\n"
     ]
    }
   ],
   "source": [
    "#基本信息\n",
    "train.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "输入特征： ID - 唯一ID（不能用于预测） Gender - 性别 City - 城市 Monthly_Income - 月收入（以卢比为单位） DOB - 出生日期 Lead_Creation_Date - 潜在（贷款）创建日期 Loan_Amount_Applied - 贷款申请请求金额（印度卢比，INR） Loan_Tenure_Applied - 贷款申请期限（单位为年） Existing_EMI -现有贷款的EMI（EMI：电子货币机构许可证） Employer_Name雇主名称 Salary_Account - 薪资帐户银行 Mobile_Verified - 是否移动验证（Y / N） VAR5 - 连续型变量 VAR1- 类别型变量 Loan_Amount_Submitted - 提交的贷款金额（在看到资格后修改和选择） Loan_Tenure_Submitted - 提交的贷款期限（单位为年，在看到资格后修改和选择） Interest_Rate - 提交贷款金额的利率 Processing_Fee - 提交贷款的处理费（INR） EMI_Loan_Submitted -提交的EMI贷款金额（INR） Filled_Form - 后期报价后是否已填写申请表格 Device_Type - 进行申请的设备（浏览器/移动设备） Var2 - 类别型变量 Source - 类别型变量 Var4 - 类别型变量\n",
    "\n",
    "输出： LoggedIn - 是否login（只用于理解问题的变量 - 不能用于预测，测试集中没有） Disbursed - 是否发放贷款（目标变量）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAZUAAAEKCAYAAADaa8itAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMS4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvNQv5yAAAFpFJREFUeJzt3Xu0JWV95vHvw02Qi4C2LtNAGmNHJTreWoImEy8oAk5oZEGCyxEGmcFkAHESHTHJsuNdEoRI4iWsQLiMw0UGtEWUQQSdOIo0F8UGWXQwQg8EWkFEGGEafvPHfg/s7j6Xorv2OWzP97PWXqfqrbdq/7ar5VlVb9VbqSokSerDZnNdgCTpV4ehIknqjaEiSeqNoSJJ6o2hIknqjaEiSeqNoSJJ6o2hIknqjaEiSerNFnNdwGx7xjOeUYsWLZrrMiRpbFxzzTU/qaoFXfrOu1BZtGgRK1asmOsyJGlsJPlx175e/pIk9cZQkST1xlCRJPXGUJEk9cZQkST1xlCRJPXGUJEk9cZQkST1xlCRJPVm3j1Rv6le/p6z5roEPQld89eHzXUJ0pOCZyqSpN4YKpKk3hgqkqTeGCqSpN4YKpKk3hgqkqTeGCqSpN4YKpKk3hgqkqTejDRUkvyXJCuT/CDJOUm2TrJ7kquS3JLkvCRbtb5Paeur2vZFQ8d5X2u/Ockbh9r3bW2rkhw/yt8iSZrZyEIlyULgncCSqnohsDlwKHACcHJVLQbuBY5suxwJ3FtVzwVObv1Iskfb77eAfYFPJ9k8yebAp4D9gD2At7S+kqQ5MurLX1sA2yTZAngqcCfwOuCCtv1M4MC2vLSt07bvnSSt/dyqeqiqfgSsAvZsn1VVdWtVPQyc2/pKkubIyEKlqv4PcCJwG4MwuQ+4BvhZVa1t3VYDC9vyQuD2tu/a1v/pw+3r7TNVuyRpjozy8tdODM4cdgd+DdiWwaWq9dXELlNse6Ltk9VyVJIVSVasWbNmptIlSRtplJe/Xg/8qKrWVNX/Ay4EXgXs2C6HAewC3NGWVwO7ArTtTwPuGW5fb5+p2jdQVadW1ZKqWrJgwYI+fpskaRKjDJXbgL2SPLWNjewN3AhcARzc+hwOfLEtL2/rtO1fr6pq7Ye2u8N2BxYD3wWuBha3u8m2YjCYv3yEv0eSNIORvaSrqq5KcgFwLbAWuA44FfgycG6SD7e209oupwFnJ1nF4Azl0HaclUnOZxBIa4Gjq+oRgCTHAJcyuLPs9KpaOarfI0ma2Ujf/FhVy4Bl6zXfyuDOrfX7/hI4ZIrjfAT4yCTtlwCXbHqlkqQ++ES9JKk3hookqTeGiiSpN4aKJKk3hookqTeGiiSpN4aKJKk3hookqTeGiiSpN4aKJKk3hookqTeGiiSpN4aKJKk3hookqTeGiiSpN4aKJKk3hookqTeGiiSpN4aKJKk3hookqTeGiiSpN4aKJKk3M4ZKkm2TbNaWfzPJAUm2HH1pkqRx0+VM5ZvA1kkWApcDRwBnjLIoSdJ46hIqqaoHgYOAv62qNwN7jLYsSdI46hQqSV4JvBX4cmvbYnQlSZLGVZdQeRfwPuCiqlqZ5DnAFaMtS5I0jmY846iqbwDfSLJtW78VeOeoC5MkjZ8ud3+9MsmNwE1t/cVJPj3yyiRJY6fL5a+/Ad4I/BSgqr4H/N4oi5IkjadODz9W1e3rNT0yglokSWOuy11ctyd5FVBJtmIwnnLTaMuSJI2jLmcqfwQcDSwEVgMvaeuSJK2jy91fP2HwjIokSdPqcvfXmUl2HFrfKcnpoy1LkjSOulz++jdV9bOJlaq6F3jp6EqSJI2rLqGyWZKdJlaS7IzTtEiSJtElHD4B/O8kF7T1Q4CPjK4kSdK46jJQf1aSa4DXAgEOqqobR16ZJGnsdH3z4w+BC4EvAr9IsluXnZLsmOSCJD9MclOb8mXnJJcluaX93an1TZJTkqxK8v0kLxs6zuGt/y1JDh9qf3mSG9o+pyRJ958uSepbl7u/jgXuAi4DLmYw/f3FHY//SeCrVfV84MUMHpo8Hri8qhYzeOnX8a3vfsDi9jkK+Ez7/p2BZcBvA3sCy4bGeD7T+k7st2/HuiRJI9BlTOU44HlV9dMncuAkOzCYI+w/AFTVw8DDSZYCr2ndzgSuBN4LLAXOqqoCvtPOcp7d+l5WVfe0414G7JvkSmCHqvp2az8LOBD4yhOpU5LUny6Xv24H7tuIYz8HWAP8Y5LrkvxDmz7/WVV1J0D7+8zWf2H7rgmrW9t07asnaZckzZEuZyq3Alcm+TLw0ERjVZ3U4dgvA46tqquSfJLHL3VNZrLxkNqI9g0PnBzF4DIZu+3WaThIkrQRupyp3MZgPGUrYPuhz0xWA6ur6qq2fgGDkLmrXdai/b17qP+uQ/vvAtwxQ/suk7RvoKpOraolVbVkwYIFHUqXJG2MLrcUfwAgybZV9UDXA1fVvya5PcnzqupmYG/gxvY5HPh4+/vFtsty4Jgk5zIYlL+vqu5Mcinw0aHB+X2A91XVPUnuT7IXcBVwGPC3XeuTJPVvxlBJ8krgNGA7YLckLwbeUVX/ucPxjwU+16bMvxU4gsHZ0flJjmRwFnRI63sJsD+wCniw9aWFx4eAq1u/D04M2gN/DJwBbMNggN5BekmaQ13GVCbe/LgcBm9+TNLpzY9VdT2wZJJNe0/St5hiSv2qOh3YYBLLqloBvLBLLZKk0fPNj5Kk3vjmR0lSb3zzoySpN9OeqSTZHHhbVfnmR0nSjKY9U6mqRxhMnyJJ0oy6jKl8K8nfAecBjz2nUlXXjqwqSdJY6hIqr2p/PzjUVsDr+i9HkjTOZhpT2Qz4TFWdP0v1SJLG2ExjKo8Cx8xSLZKkMdflluLLkrw7ya7trY07txdnSZK0ji5jKm9vf4efTSkG70uRJOkxXWYp3n02CpEkjb8usxQfNll7VZ3VfzmSpHHW5fLXK4aWt2Yww/C1gKEiSVpHl8tfxw6vJ3kacPbIKpIkja1OU9+v50Fgcd+FSJLGX5cxlS8xuNsLBiG0B+DDkJKkDXQZUzlxaHkt8OOqWj2ieiRJY6xLqNwG3FlVvwRIsk2SRVX1LyOtTJI0drqMqXweeHRo/ZHWJknSOrqEyhZV9fDESlveanQlSZLGVZdQWZPkgImVJEuBn4yuJEnSuOoypvJHwOfai7pg8J76SZ+ylyTNb10efvxnYK8k2wGpqvtHX5YkaRzNePkryUeT7FhVv6iq+5PslOTDs1GcJGm8dBlT2a+qfjaxUlX3AvuPriRJ0rjqEiqbJ3nKxEqSbYCnTNNfkjRPdRmo/2/A5Un+kcF0LW8HzhxpVZKksdRloP6vknwfeH1r+lBVXTrasiRJ46jLmQrAdcCWDM5UrhtdOZKkcdbl7q8/AL4LHAz8AXBVkoNHXZgkafx0OVP5c+AVVXU3QJIFwNeAC0ZZmCRp/HS5+2uziUBpftpxP0nSPNPlTOWrSS4FzmnrfwhcMrqSJEnjqsvdX+9JchDwu0CAU6vqopFXJkkaO53u/qqqC4ELR1yLJGnMOTYiSeqNoSJJ6s2UoZLk8vb3hNkrR5I0zqYbU3l2klcDByQ5l8Eg/WOq6tqRViZJGjvTXf56P3A8sAtwEvCJoc+JXb8gyeZJrktycVvfPclVSW5Jcl6SrVr7U9r6qrZ90dAx3tfab07yxqH2fVvbqiTHd//ZkqRRmDJUquqCqtoP+Kuqeu16n9c9ge84DrhpaP0E4OSqWgzcCxzZ2o8E7q2q5wInt34k2QM4FPgtYF/g0y2oNgc+BewH7AG8pfWVJM2RGQfqq+pDSQ5IcmL7/LuuB0+yC/Am4B/aeoDX8fgUL2cCB7blpTw+pf4FwN6t/1Lg3Kp6qKp+BKwC9myfVVV1a1U9DJzb+kqS5kiXCSU/xuBs48b2Oa61dfE3wH8FHm3rTwd+VlVr2/pqYGFbXgjcDtC239f6P9a+3j5TtU/2G45KsiLJijVr1nQsXZL0RHW5pfhNwBuq6vSqOp3BJag3zbRTO6O5u6quGW6epGvNsO2Jtm/YWHVqVS2pqiULFiyYpmpJ0qbo+j6VHYF72vLTOu7zOwzuHNsf2BrYgcGZy45JtmhnI7sAd7T+q4FdgdVJtmjfc89Q+4ThfaZqlyTNgS5nKh8DrktyRpIzgWuAj860U1W9r6p2qapFDAbav15VbwWuYPBuFoDDgS+25eVtnbb961VVrf3QdnfY7sBiBu93uRpY3O4m26p9x/IOv0eSNCJdJpQ8J8mVwCsYXHJ6b1X96yZ853uBc5N8mMFbJE9r7acBZydZxeAM5dD2/SuTnM9gPGctcHRVPQKQ5BjgUmBz4PSqWrkJdUmSNlHXCSXvZBPOAqrqSuDKtnwrgzu31u/zS+CQKfb/CPCRSdovwWn4JelJw7m/JEm9MVQkSb2ZNlSSbJbkB7NVjCRpvE0bKlX1KPC9JLvNUj2SpDHWZaD+2cDKJN8FHphorKoDRlaVJGksdQmVD4y8CknSr4Quz6l8I8mvA4ur6mtJnsrguRBJktbRZULJ/8Rg1uC/b00LgS+MsihJ0njqckvx0Qzm8fo5QFXdAjxzlEVJksZTl1B5qL2vBIA22eOkswFLkua3LqHyjSR/BmyT5A3A54EvjbYsSdI46hIqxwNrgBuAdzCYa+svRlmUJGk8dbn769E25f1VDC573dympJckaR0zhkqSNwGfBf6ZwdT3uyd5R1V9ZdTFSZLGS5eHHz8BvLaqVgEk+Q3gy4ChIklaR5cxlbsnAqW5Fbh7RPVIksbYlGcqSQ5qiyuTXAKcz2BM5RAGr/KVJGkd013++v2h5buAV7flNcBOI6tIkjS2pgyVqjpiNguRJI2/Lnd/7Q4cCywa7u/U95Kk9XW5++sLwGkMnqJ/dLTlSJLGWZdQ+WVVnTLySiRJY69LqHwyyTLgfwIPTTRW1bUjq0qSNJa6hMqLgLcBr+Pxy1/V1iVJekyXUHkz8Jzh6e8lSZpMlyfqvwfsOOpCJEnjr8uZyrOAHya5mnXHVLylWJK0ji6hsmzkVUiSfiV0eZ/KN2ajEEnS+OvyRP39PP5O+q2ALYEHqmqHURYmSRo/Xc5Uth9eT3IgsOfIKpIkja0ud3+to6q+gM+oSJIm0eXy10FDq5sBS3j8cpgkSY/pcvfX8HtV1gL/AiwdSTWSpLHWZUzF96pIkjqZ7nXC759mv6qqD42gHknSGJvuTOWBSdq2BY4Eng4YKpKkdUz3OuFPTCwn2R44DjgCOBf4xFT7SZLmr2nHVJLsDPwJ8FbgTOBlVXXvbBQmSRo/Uz6nkuSvgauB+4EXVdVfPpFASbJrkiuS3JRkZZLjWvvOSS5Lckv7u1NrT5JTkqxK8v0kLxs61uGt/y1JDh9qf3mSG9o+pyTJRvxvIEnqyXQPP/4p8GvAXwB3JPl5+9yf5Ocdjr0W+NOqegGwF3B0kj2A44HLq2oxcHlbB9gPWNw+RwGfgcfOlpYBv83gSf5lE0HU+hw1tN++3X62JGkUpgyVqtqsqrapqu2raoehz/Zd5v2qqjsnXjlcVfcDNwELGTzjcmbrdiZwYFteCpxVA98BdkzybOCNwGVVdU87U7oM2Ldt26Gqvl1VBZw1dCxJ0hx4wtO0bIwki4CXAlcBz6qqO2EQPMAzW7eFwO1Du61ubdO1r56kfbLvPyrJiiQr1qxZs6k/R5I0hZGHSpLtgP8BvKuqprtsNtl4SG1E+4aNVadW1ZKqWrJgwYKZSpYkbaSRhkqSLRkEyueq6sLWfFe7dEX7e3drXw3sOrT7LsAdM7TvMkm7JGmOjCxU2p1YpwE3VdVJQ5uWAxN3cB0OfHGo/bB2F9hewH3t8tilwD5JdmoD9PsAl7Zt9yfZq33XYUPHkiTNgS4TSm6s3wHeBtyQ5PrW9mfAx4HzkxwJ3AYc0rZdAuwPrAIeZPCgJVV1T5IPMbi9GeCDVXVPW/5j4AxgG+Ar7SNJmiMjC5Wq+icmH/cA2HuS/gUcPcWxTgdOn6R9BfDCTShTktSjWbn7S5I0PxgqkqTeGCqSpN4YKpKk3hgqkqTeGCqSpN4YKpKk3hgqkqTeGCqSpN4YKpKk3hgqkqTeGCqSpN4YKpKk3hgqkqTeGCqSpN4YKpKk3hgqkqTeGCqSpN4YKpKk3hgqkqTeGCqSpN4YKpKk3hgqkqTeGCqSpN4YKpKk3hgqkqTeGCqSpN4YKpKk3hgqkqTeGCqSpN4YKpKk3hgqkqTeGCqSpN4YKpKk3hgqkqTeGCqSpN4YKpKk3hgqkqTeGCqSpN6Mfagk2TfJzUlWJTl+ruuRpPlsi7kuYFMk2Rz4FPAGYDVwdZLlVXXj3FYmzY3bPviiuS5BT0K7vf+GWfuucT9T2RNYVVW3VtXDwLnA0jmuSZLmrXEPlYXA7UPrq1ubJGkOjPXlLyCTtNUGnZKjgKPa6i+S3DzSquaPZwA/mesingxy4uFzXYI25L/PCcsm+0/lE/LrXTuOe6isBnYdWt8FuGP9TlV1KnDqbBU1XyRZUVVL5roOaTL++5wb437562pgcZLdk2wFHAosn+OaJGneGuszlapam+QY4FJgc+D0qlo5x2VJ0rw11qECUFWXAJfMdR3zlJcU9WTmv885kKoNxrUlSdoo4z6mIkl6EjFUNKOZpsJJ8pQk57XtVyVZNPtVaj5KcnqSu5P8YIrtSXJK+7f5/SQvm+0a5xtDRdMamgpnP2AP4C1J9liv25HAvVX1XOBk4ITZrVLz2BnAvtNs3w9Y3D5HAZ+ZhZrmNUNFM+kyFc5S4My2fAGwd5JNftpKmklVfRO4Z5ouS4GzauA7wI5Jnj071c1Phopm0mUqnMf6VNVa4D7g6bNSnTQ9p3KaZYaKZtJlKpxO0+VIc8B/m7PMUNFMukyF81ifJFsAT2P6SxLSbOk0lZP6Y6hoJl2mwlkOTMyoeDDw9fIBKD05LAcOa3eB7QXcV1V3znVRv8rG/ol6jdZUU+Ek+SCwoqqWA6cBZydZxeAM5dC5q1jzSZJzgNcAz0iyGlgGbAlQVZ9lMNvG/sAq4EHgiLmpdP7wiXpJUm+8/CVJ6o2hIknqjaEiSeqNoSJJ6o2hIknqjaEidZTkkSTXJ1mZ5HtJ/iTJZm3bkiSnTLPva5JcPHvVbvD9f5nk3XP1/Zo/fE5F6u7/VtVLAJI8E/jvDGYPWFZVK4AVo/riJFu0edWkJzXPVKSNUFV3M5hK/Zj2tPZjZyJJXt3OaK5Pcl2S7dtuOyS5KMmNST47dJbzi4njJjk4yRlt+YwkJyW5AjhhquMmeU+Sq9v7Qj4wdKw/b+/B+RrwvNn430XyTEXaSFV1awuGZ6636d3A0VX1rSTbAb9s7XsyeCfNj4GvAgcxeFXAdH4TeH1VPZLkS+sfN8k+DN4VsieDyROXJ/k94AEGMxu8lMH/z68Frtm0XyzNzDMVadNMNgvut4CTkrwT2HHostV323tpHgHOAX63w/E/3/pPddx92uc6BsHxfAYh82+Bi6rqwar6ORvO1yaNhKEibaQkzwEeAe4ebq+qjwP/EdgG+E6S509sWu8QNUn71uv1eWCG4wb4WFW9pH2eW1WnTfF90sgZKtJGSLIA+Czwd+vPyJzkN6rqhqo6gcHg/USo7Nlme94M+EPgn1r7XUle0NrfPM13TnbcS4G3t8thJFnYbiL4JvDmJNu0sZff7+u3S9NxTEXqbpsk1zOYBXctcDZw0iT93pXktQzOYm4EvgK8Evg28HHgRQz+o39R6388cDGDNxT+ANhuiu/f4LhV9VCSFwDfbm9w/gXw76vq2iTnAdczGMP5X5vyw6WunKVYktQbL39JknpjqEiSemOoSJJ6Y6hIknpjqEiSemOoSJJ6Y6hIknpjqEiSevP/AQfaa5oYJqxDAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x12910128>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Target 分布\n",
    "sns.countplot(train['Disbursed']);\n",
    "plt.xlabel('Disbursed');\n",
    "plt.ylabel('Number of occurrences');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "从分布图中我们可以看出，两类样本分布严重不均衡，发放贷款的比例甚至可以小到忽略，说明贷款发放条件十分苛刻。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Monthly_Income</th>\n",
       "      <th>Loan_Amount_Applied</th>\n",
       "      <th>Loan_Tenure_Applied</th>\n",
       "      <th>Existing_EMI</th>\n",
       "      <th>Var5</th>\n",
       "      <th>Loan_Amount_Submitted</th>\n",
       "      <th>Loan_Tenure_Submitted</th>\n",
       "      <th>Interest_Rate</th>\n",
       "      <th>Processing_Fee</th>\n",
       "      <th>EMI_Loan_Submitted</th>\n",
       "      <th>Var4</th>\n",
       "      <th>LoggedIn</th>\n",
       "      <th>Disbursed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>8.702000e+04</td>\n",
       "      <td>8.694900e+04</td>\n",
       "      <td>86949.000000</td>\n",
       "      <td>8.694900e+04</td>\n",
       "      <td>87020.000000</td>\n",
       "      <td>5.240700e+04</td>\n",
       "      <td>52407.000000</td>\n",
       "      <td>27726.000000</td>\n",
       "      <td>27420.000000</td>\n",
       "      <td>27726.000000</td>\n",
       "      <td>87020.000000</td>\n",
       "      <td>87020.000000</td>\n",
       "      <td>87019.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>5.884997e+04</td>\n",
       "      <td>2.302507e+05</td>\n",
       "      <td>2.131399</td>\n",
       "      <td>3.696228e+03</td>\n",
       "      <td>4.961503</td>\n",
       "      <td>3.950106e+05</td>\n",
       "      <td>3.891369</td>\n",
       "      <td>19.197474</td>\n",
       "      <td>5131.150839</td>\n",
       "      <td>10999.528377</td>\n",
       "      <td>2.949793</td>\n",
       "      <td>0.029350</td>\n",
       "      <td>0.014629</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.177511e+06</td>\n",
       "      <td>3.542068e+05</td>\n",
       "      <td>2.014193</td>\n",
       "      <td>3.981021e+04</td>\n",
       "      <td>5.670385</td>\n",
       "      <td>3.082481e+05</td>\n",
       "      <td>1.165359</td>\n",
       "      <td>5.834213</td>\n",
       "      <td>4725.837644</td>\n",
       "      <td>7512.323050</td>\n",
       "      <td>1.697736</td>\n",
       "      <td>0.168785</td>\n",
       "      <td>0.120063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.000000e+04</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>11.990000</td>\n",
       "      <td>200.000000</td>\n",
       "      <td>1176.410000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.650000e+04</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.000000e+05</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>15.250000</td>\n",
       "      <td>2000.000000</td>\n",
       "      <td>6491.600000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2.500000e+04</td>\n",
       "      <td>1.000000e+05</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>3.000000e+05</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>4000.000000</td>\n",
       "      <td>9392.970000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>4.000000e+04</td>\n",
       "      <td>3.000000e+05</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>3.500000e+03</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>5.000000e+05</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>6250.000000</td>\n",
       "      <td>12919.040000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>4.445544e+08</td>\n",
       "      <td>1.000000e+07</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>1.000000e+07</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>3.000000e+06</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>37.000000</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>144748.280000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Monthly_Income  Loan_Amount_Applied  Loan_Tenure_Applied  Existing_EMI  \\\n",
       "count    8.702000e+04         8.694900e+04         86949.000000  8.694900e+04   \n",
       "mean     5.884997e+04         2.302507e+05             2.131399  3.696228e+03   \n",
       "std      2.177511e+06         3.542068e+05             2.014193  3.981021e+04   \n",
       "min      0.000000e+00         0.000000e+00             0.000000  0.000000e+00   \n",
       "25%      1.650000e+04         0.000000e+00             0.000000  0.000000e+00   \n",
       "50%      2.500000e+04         1.000000e+05             2.000000  0.000000e+00   \n",
       "75%      4.000000e+04         3.000000e+05             4.000000  3.500000e+03   \n",
       "max      4.445544e+08         1.000000e+07            10.000000  1.000000e+07   \n",
       "\n",
       "               Var5  Loan_Amount_Submitted  Loan_Tenure_Submitted  \\\n",
       "count  87020.000000           5.240700e+04           52407.000000   \n",
       "mean       4.961503           3.950106e+05               3.891369   \n",
       "std        5.670385           3.082481e+05               1.165359   \n",
       "min        0.000000           5.000000e+04               1.000000   \n",
       "25%        0.000000           2.000000e+05               3.000000   \n",
       "50%        2.000000           3.000000e+05               4.000000   \n",
       "75%       11.000000           5.000000e+05               5.000000   \n",
       "max       18.000000           3.000000e+06               6.000000   \n",
       "\n",
       "       Interest_Rate  Processing_Fee  EMI_Loan_Submitted          Var4  \\\n",
       "count   27726.000000    27420.000000        27726.000000  87020.000000   \n",
       "mean       19.197474     5131.150839        10999.528377      2.949793   \n",
       "std         5.834213     4725.837644         7512.323050      1.697736   \n",
       "min        11.990000      200.000000         1176.410000      0.000000   \n",
       "25%        15.250000     2000.000000         6491.600000      1.000000   \n",
       "50%        18.000000     4000.000000         9392.970000      3.000000   \n",
       "75%        20.000000     6250.000000        12919.040000      5.000000   \n",
       "max        37.000000    50000.000000       144748.280000      7.000000   \n",
       "\n",
       "           LoggedIn     Disbursed  \n",
       "count  87020.000000  87019.000000  \n",
       "mean       0.029350      0.014629  \n",
       "std        0.168785      0.120063  \n",
       "min        0.000000      0.000000  \n",
       "25%        0.000000      0.000000  \n",
       "50%        0.000000      0.000000  \n",
       "75%        0.000000      0.000000  \n",
       "max        1.000000      1.000000  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(124737, 27)"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#合成一个总的data，方便一起做特征工程\n",
    "train['source']= 'train'\n",
    "test['source'] = 'test'\n",
    "data = pd.concat([train, test],ignore_index=True)\n",
    "data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 检查数据质量：异常点、缺省值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "City                      1401\n",
       "DOB                          0\n",
       "Device_Type                  0\n",
       "Disbursed                37718\n",
       "EMI_Loan_Submitted       84901\n",
       "Employer_Name              113\n",
       "Existing_EMI               111\n",
       "Filled_Form                  0\n",
       "Gender                       0\n",
       "ID                           0\n",
       "Interest_Rate            84901\n",
       "Lead_Creation_Date           0\n",
       "Loan_Amount_Applied        111\n",
       "Loan_Amount_Submitted    49535\n",
       "Loan_Tenure_Applied        111\n",
       "Loan_Tenure_Submitted    49535\n",
       "LoggedIn                 37717\n",
       "Mobile_Verified              0\n",
       "Monthly_Income               0\n",
       "Processing_Fee           85346\n",
       "Salary_Account           16801\n",
       "Source                       0\n",
       "Var1                         1\n",
       "Var2                         0\n",
       "Var4                         0\n",
       "Var5                         0\n",
       "source                       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.apply(lambda x: sum(x.isnull()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 类别型特征的分布"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Gender属性有2的不同取值，各取值及其出现的次数\n",
      "\n",
      "Male      71398\n",
      "Female    53339\n",
      "Name: Gender, dtype: int64\n",
      "\n",
      "City属性有724的不同取值，各取值及其出现的次数\n",
      "\n",
      "Delhi                  17936\n",
      "Bengaluru              15522\n",
      "Mumbai                 15425\n",
      "Hyderabad              10410\n",
      "Chennai                 9895\n",
      "Pune                    7427\n",
      "Kolkata                 4282\n",
      "Ahmedabad               2528\n",
      "Jaipur                  1892\n",
      "Gurgaon                 1743\n",
      "Coimbatore              1659\n",
      "Thane                   1306\n",
      "Chandigarh              1266\n",
      "Surat                   1149\n",
      "Visakhapatnam           1080\n",
      "Indore                  1051\n",
      "Vadodara                 893\n",
      "Nagpur                   879\n",
      "Lucknow                  813\n",
      "Ghaziabad                795\n",
      "Bhopal                   735\n",
      "Kochi                    692\n",
      "Patna                    675\n",
      "Faridabad                651\n",
      "Noida                    549\n",
      "Madurai                  534\n",
      "Gautam Buddha Nagar      485\n",
      "Dehradun                 444\n",
      "Raipur                   430\n",
      "Bhubaneswar              407\n",
      "                       ...  \n",
      "Tawang                     1\n",
      "LUNAWADA                   1\n",
      "Sawai Madhopur             1\n",
      "Anjaw                      1\n",
      "Bandipore                  1\n",
      "Lohit                      1\n",
      "Geyzing                    1\n",
      "Kupwara                    1\n",
      "Madhepura                  1\n",
      "Kandhamal                  1\n",
      "Chinnamiram                1\n",
      "Mainpuri                   1\n",
      "KAMREJ                     1\n",
      "Karim Ganj                 1\n",
      "CHOTILA                    1\n",
      "Saiha                      1\n",
      "Seoni                      1\n",
      "SANAND                     1\n",
      "Khagaria                   1\n",
      "Panna                      1\n",
      "Mokokchung                 1\n",
      "Sheikhpura                 1\n",
      "Leh                        1\n",
      "Champhai                   1\n",
      "Kannauj                    1\n",
      "GANDEVI                    1\n",
      "Haldia                     1\n",
      "Bageshwar                  1\n",
      "SAYAN                      1\n",
      "Baksa                      1\n",
      "Name: City, Length: 723, dtype: int64\n",
      "\n",
      "Employer_Name属性有57195的不同取值，各取值及其出现的次数\n",
      "\n",
      "0                                               6900\n",
      "TATA CONSULTANCY SERVICES LTD (TCS)              754\n",
      "COGNIZANT TECHNOLOGY SOLUTIONS INDIA PVT LTD     558\n",
      "ACCENTURE SERVICES PVT LTD                       476\n",
      "GOOGLE                                           408\n",
      "ICICI BANK LTD                                   337\n",
      "HCL TECHNOLOGIES LTD                             337\n",
      "IBM CORPORATION                                  265\n",
      "INDIAN AIR FORCE                                 258\n",
      "INFOSYS TECHNOLOGIES                             257\n",
      "INDIAN ARMY                                      243\n",
      "GENPACT                                          240\n",
      "WIPRO TECHNOLOGIES                               235\n",
      "TYPE SLOWLY FOR AUTO FILL                        219\n",
      "IKYA HUMAN CAPITAL SOLUTIONS LTD                 204\n",
      "ARMY                                             203\n",
      "INDIAN RAILWAY                                   201\n",
      "HDFC BANK LTD                                    201\n",
      "STATE GOVERNMENT                                 199\n",
      "WIPRO BPO                                        186\n",
      "INDIAN NAVY                                      183\n",
      "CONVERGYS INDIA SERVICES PVT LTD                 165\n",
      "OTHERS                                           159\n",
      "TECH MAHINDRA LTD                                158\n",
      "IBM GLOBAL SERVICES INDIA LTD                    158\n",
      "CONCENTRIX DAKSH SERVICES INDIA PVT LTD          154\n",
      "CAPGEMINI INDIA PVT LTD                          152\n",
      "SERCO BPO PVT LTD                                149\n",
      "SUTHERLAND GLOBAL SERVICES PVT LTD               141\n",
      "ADECCO INDIA PVT LTD                             140\n",
      "                                                ... \n",
      "AG2                                                1\n",
      "ZILHA PARISHAD                                     1\n",
      "EASYTECH SERVICES PVT.LTD.                         1\n",
      "M.S.KHURANA ENG LTD                                1\n",
      "SHREENATH PETROLEUM                                1\n",
      "PARADIGM GEOPHYSICAL INDIA PVT LTD                 1\n",
      "RESHMA                                             1\n",
      "INDIAN AIRFORCE STATION TAMBARAM                   1\n",
      "SAPANA POLYWEAVE PVT.LTD                           1\n",
      "DEPARTMENT OF PUBLIC INSTRUCTIONS                  1\n",
      "SATENDER                                           1\n",
      "A P SECURSECURITAS PVT LTD                         1\n",
      "PROLEADS CONSULTING PVT LTD                        1\n",
      "HITECH SOLUTIONS                                   1\n",
      "FARHAT RAZZAK NAISARGI                             1\n",
      "WELL-KNOWN POLYESTER LTD DAMAN                     1\n",
      "MAHESHKUMAR SAKHRAM PILKE                          1\n",
      "JUPITER INTERNATIONAL LTD                          1\n",
      "WORKFORCE                                          1\n",
      "SURAJMAL SR.SEC SCHOOL                             1\n",
      "CLARIANT CHEMICALS INDIA LTD                       1\n",
      "R AND R HOSPITAL                                   1\n",
      "HINDUJA VENTURES LTD                               1\n",
      "ALLIANCE ENGINEERS                                 1\n",
      "AVL TECHNICAL CENTER                               1\n",
      "MILITARY INT.TRAINING SCHOOL                       1\n",
      "KAMALESH GREENCRETE PVT LTD                        1\n",
      "ASTHA MOBILE SHOP                                  1\n",
      "BMP E GROUP SOLUTION PVT LTD                       1\n",
      "PARVEEN TAJ                                        1\n",
      "Name: Employer_Name, Length: 57194, dtype: int64\n",
      "\n",
      "Salary_Account属性有60的不同取值，各取值及其出现的次数\n",
      "\n",
      "HDFC Bank                                          25180\n",
      "ICICI Bank                                         19547\n",
      "State Bank of India                                17110\n",
      "Axis Bank                                          12590\n",
      "Citibank                                            3398\n",
      "Kotak Bank                                          2955\n",
      "IDBI Bank                                           2213\n",
      "Punjab National Bank                                1747\n",
      "Bank of India                                       1713\n",
      "Bank of Baroda                                      1675\n",
      "Standard Chartered Bank                             1434\n",
      "Canara Bank                                         1384\n",
      "Union Bank of India                                 1330\n",
      "Yes Bank                                            1120\n",
      "ING Vysya                                            996\n",
      "Corporation bank                                     948\n",
      "Indian Overseas Bank                                 901\n",
      "State Bank of Hyderabad                              854\n",
      "Indian Bank                                          773\n",
      "Oriental Bank of Commerce                            761\n",
      "IndusInd Bank                                        711\n",
      "Andhra Bank                                          706\n",
      "Central Bank of India                                648\n",
      "Syndicate Bank                                       614\n",
      "Bank of Maharasthra                                  576\n",
      "HSBC                                                 474\n",
      "State Bank of Bikaner & Jaipur                       448\n",
      "Karur Vysya Bank                                     435\n",
      "State Bank of Mysore                                 385\n",
      "Federal Bank                                         377\n",
      "Vijaya Bank                                          354\n",
      "Allahabad Bank                                       345\n",
      "UCO Bank                                             344\n",
      "State Bank of Travancore                             333\n",
      "Karnataka Bank                                       279\n",
      "United Bank of India                                 276\n",
      "Dena Bank                                            268\n",
      "Saraswat Bank                                        265\n",
      "State Bank of Patiala                                263\n",
      "South Indian Bank                                    223\n",
      "Deutsche Bank                                        176\n",
      "Abhyuday Co-op Bank Ltd                              161\n",
      "The Ratnakar Bank Ltd                                113\n",
      "Tamil Nadu Mercantile Bank                           103\n",
      "Punjab & Sind bank                                    84\n",
      "J&K Bank                                              78\n",
      "Lakshmi Vilas bank                                    69\n",
      "Dhanalakshmi Bank Ltd                                 66\n",
      "State Bank of Indore                                  32\n",
      "Catholic Syrian Bank                                  27\n",
      "India Bulls                                           21\n",
      "B N P Paribas                                         15\n",
      "Firstrand Bank Limited                                11\n",
      "GIC Housing Finance Ltd                               10\n",
      "Bank of Rajasthan                                      8\n",
      "Kerala Gramin Bank                                     4\n",
      "Industrial And Commercial Bank Of China Limited        3\n",
      "N                                                      1\n",
      "Ahmedabad Mercantile Cooperative Bank                  1\n",
      "Name: Salary_Account, dtype: int64\n",
      "\n",
      "Mobile_Verified属性有3的不同取值，各取值及其出现的次数\n",
      "\n",
      "Y    80928\n",
      "N    43808\n",
      "0        1\n",
      "Name: Mobile_Verified, dtype: int64\n",
      "\n",
      "Var1属性有20的不同取值，各取值及其出现的次数\n",
      "\n",
      "HBXX    84900\n",
      "HBXC    12952\n",
      "HBXB     6502\n",
      "HAXA     4214\n",
      "HBXA     3042\n",
      "HAXB     2879\n",
      "HBXD     2818\n",
      "HAXC     2171\n",
      "HBXH     1387\n",
      "HCXF      990\n",
      "HAYT      710\n",
      "HAVC      570\n",
      "HAXM      386\n",
      "HCXD      348\n",
      "HCYS      318\n",
      "HVYS      252\n",
      "HAZD      161\n",
      "HCXG      114\n",
      "HAXF       22\n",
      "Name: Var1, dtype: int64\n",
      "\n",
      "Filled_Form属性有3的不同取值，各取值及其出现的次数\n",
      "\n",
      "N         96739\n",
      "Y         27997\n",
      "Mobile        1\n",
      "Name: Filled_Form, dtype: int64\n",
      "\n",
      "Device_Type属性有3的不同取值，各取值及其出现的次数\n",
      "\n",
      "Web-browser    92105\n",
      "Mobile         32631\n",
      "G                  1\n",
      "Name: Device_Type, dtype: int64\n",
      "\n",
      "Var2属性有8的不同取值，各取值及其出现的次数\n",
      "\n",
      "B       53481\n",
      "G       47337\n",
      "C       20366\n",
      "E        1855\n",
      "D         918\n",
      "F         770\n",
      "A           9\n",
      "S122        1\n",
      "Name: Var2, dtype: int64\n",
      "\n",
      "Source属性有35的不同取值，各取值及其出现的次数\n",
      "\n",
      "S122    55248\n",
      "S133    42900\n",
      "S159     7999\n",
      "S143     6140\n",
      "S127     2804\n",
      "S137     2450\n",
      "S134     1900\n",
      "S161     1109\n",
      "S151     1018\n",
      "S157      929\n",
      "S153      705\n",
      "S144      447\n",
      "S156      432\n",
      "S158      294\n",
      "S123      112\n",
      "S141       83\n",
      "S162       60\n",
      "S124       43\n",
      "S150       19\n",
      "S160       11\n",
      "S136        5\n",
      "S155        5\n",
      "S138        5\n",
      "S129        4\n",
      "S139        4\n",
      "S135        2\n",
      "S130        1\n",
      "S126        1\n",
      "1           1\n",
      "S131        1\n",
      "S132        1\n",
      "S154        1\n",
      "S140        1\n",
      "S125        1\n",
      "S142        1\n",
      "Name: Source, dtype: int64\n",
      "\n",
      "Var4属性有8的不同取值，各取值及其出现的次数\n",
      "\n",
      "3    36280\n",
      "1    34315\n",
      "5    29092\n",
      "4     9411\n",
      "2     8481\n",
      "0     3565\n",
      "7     3264\n",
      "6      329\n",
      "Name: Var4, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "cat_features = ['Gender','City','Employer_Name','Salary_Account','Mobile_Verified','Var1','Filled_Form','Device_Type','Var2','Source','Var4']\n",
    "for col in cat_features:\n",
    "    num_vlaules = len(data[col].unique())\n",
    "    print ('\\n%s属性有%d的不同取值，各取值及其出现的次数\\n'% (col,num_vlaules) )\n",
    "    print(data[col].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "City、Employer_Name、Salary_Account、Source这些特征都是取值很多, 取前几个重要的，其余合并成一个：others\n",
    "\n",
    "LightGBM对类别特征建立直方图时，当特征取值数目超过max_bin(默认255)，会去掉样本数目少的类别： 统计该特征下每一种离散值出现的次数，并从高到低排序，并过滤掉出现次数较少的特征值, 然后为每一个特征值，建立一个bin容器, 对于在bin容器内出现次数较少的特征值直接过滤掉，不建立bin容器。\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_features = ['City','Employer_Name','Salary_Account', 'Source']\n",
    "rare_thresholds = [100, 30, 40, 40]\n",
    "j=0\n",
    "for col in cat_features:\n",
    "    #每个取值的样本数目\n",
    "    value_counts_col =  data[col].value_counts(dropna=False)\n",
    "\n",
    "    #样本数目小于阈值的取值为稀有取值\n",
    "    rare_threshold = rare_thresholds[j]\n",
    "    value_counts_rare = list(value_counts_col[value_counts_col < rare_threshold ].index)\n",
    "\n",
    "    #稀有值合并为：others\n",
    "    rare_index = data[col].isin(value_counts_rare)\n",
    "    data.loc[ data[col].isin(value_counts_rare), col] = \"Others\"\n",
    "    \n",
    "    j = j+1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DOB\n",
    "DOB是出生的具体日期，具体日期可能没作用，转换成年龄(申请贷款的年龄)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    37\n",
       "1    30\n",
       "2    34\n",
       "3    28\n",
       "4    31\n",
       "Name: Age, dtype: int64"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#创建一个年龄的字段Age\n",
    "data['Age'] = pd.to_datetime(data['Lead_Creation_Date']).dt.year - pd.to_datetime(data['DOB']).dt.year\n",
    "data['Age'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "#把原始的DOB字段去掉:\n",
    "data.drop(['DOB', 'Lead_Creation_Date'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loan Tenure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "#不合理的贷款年限，设为缺失值\n",
    "data['Loan_Tenure_Applied'].replace([10,6,7,8,9],value = np.nan, inplace = True)\n",
    "data['Loan_Tenure_Submitted'].replace(6, np.nan, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## LoggedIn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "#不能用于预测特征，drop\n",
    "data.drop('LoggedIn',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 类别特征先编码成数值，LightGBM无需One-hot编码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import LabelEncoder\n",
    "le = LabelEncoder()\n",
    "feats_to_encode = ['City', 'Employer_Name', 'Salary_Account','Device_Type','Filled_Form','Gender','Mobile_Verified','Source','Var1','Var2','Var4']\n",
    "for col in feats_to_encode:\n",
    "    data[col] = le.fit_transform(data[col].astype(str))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 最终的数据样式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>Device_Type</th>\n",
       "      <th>Disbursed</th>\n",
       "      <th>EMI_Loan_Submitted</th>\n",
       "      <th>Employer_Name</th>\n",
       "      <th>Existing_EMI</th>\n",
       "      <th>Filled_Form</th>\n",
       "      <th>Gender</th>\n",
       "      <th>ID</th>\n",
       "      <th>Interest_Rate</th>\n",
       "      <th>...</th>\n",
       "      <th>Monthly_Income</th>\n",
       "      <th>Processing_Fee</th>\n",
       "      <th>Salary_Account</th>\n",
       "      <th>Source</th>\n",
       "      <th>Var1</th>\n",
       "      <th>Var2</th>\n",
       "      <th>Var4</th>\n",
       "      <th>Var5</th>\n",
       "      <th>source</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>192</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>ID000002C20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>20000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>train</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>44</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6762.9</td>\n",
       "      <td>227</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>ID000004E40</td>\n",
       "      <td>13.25</td>\n",
       "      <td>...</td>\n",
       "      <td>35000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>train</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>52</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>192</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>ID000007H20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>22500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37</td>\n",
       "      <td>9</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>train</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>52</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>192</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>ID000008I30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>35000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37</td>\n",
       "      <td>9</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>train</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>192</td>\n",
       "      <td>25000.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>ID000009J40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>100000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>17</td>\n",
       "      <td>train</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   City  Device_Type  Disbursed  EMI_Loan_Submitted  Employer_Name  \\\n",
       "0    15            2        0.0                 NaN            192   \n",
       "1    44            2        0.0              6762.9            227   \n",
       "2    52            2        0.0                 NaN            192   \n",
       "3    52            2        0.0                 NaN            192   \n",
       "4     6            2        0.0                 NaN            192   \n",
       "\n",
       "   Existing_EMI  Filled_Form  Gender           ID  Interest_Rate ...   \\\n",
       "0           0.0            1       0  ID000002C20            NaN ...    \n",
       "1           0.0            1       1  ID000004E40          13.25 ...    \n",
       "2           0.0            1       1  ID000007H20            NaN ...    \n",
       "3           0.0            1       1  ID000008I30            NaN ...    \n",
       "4       25000.0            1       1  ID000009J40            NaN ...    \n",
       "\n",
       "   Monthly_Income  Processing_Fee  Salary_Account  Source  Var1  Var2  Var4  \\\n",
       "0           20000             NaN              15       1    13     6     1   \n",
       "1           35000             NaN              17       1     8     6     3   \n",
       "2           22500             NaN              37       9    13     1     1   \n",
       "3           35000             NaN              37       9    13     1     3   \n",
       "4          100000             NaN              15       6    13     1     3   \n",
       "\n",
       "   Var5  source  Age  \n",
       "0     0   train   37  \n",
       "1    13   train   30  \n",
       "2     0   train   34  \n",
       "3    10   train   28  \n",
       "4    17   train   31  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 区分训练和测试数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "train = data.loc[data['source']=='train']\n",
    "test = data.loc[data['source']=='test']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.drop('source',axis=1,inplace=True)\n",
    "test.drop(['source','Disbursed'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.to_csv('FE_train.csv',index=False)\n",
    "test.to_csv('FE_test.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
